import pandas as pd

# 读取主设备文件时指定编码列为字符串
main_df = pd.read_excel(
    r'D:\xlzx\4G5G资料20220610\2025\主设备2-17.xlsx',
    dtype={'铁塔站址编码': str}  # 关键修改
)
main_df['铁塔站址编码'] = main_df['铁塔站址编码'].astype(str).str.strip()
tower_file = r'D:\\xlzx\\铁塔\\2025\\02.06-电信使用站址清单-运维反馈(1).xlsx'
sheets = pd.read_excel(tower_file, sheet_name=None)

main_df['经度'] = None
main_df['纬度'] = None

for sheet_name, df in sheets.items():
    # 预处理铁塔清单数据
    tower_df = df[['站址编码', '经度', '纬度']].copy()
    tower_df['站址编码'] = tower_df['站址编码'].astype(str).str.strip()  # 转换为字符串并去除空格
        # 新增去重处理（保留每个sheet中最后出现的记录）
    tower_df = tower_df.drop_duplicates(subset='站址编码', keep='last')
    merged = main_df.merge(
        tower_df,
        left_on='铁塔站址编码',
        right_on='站址编码',
        how='left',
        suffixes=('', '_new'),
        indicator=True
    )
    
    # 筛选有效匹配（同时检查合并状态和原始空值）
    update_mask = merged['经度_new'].notna() & merged['经度'].isna()
    
    # 使用.values避免索引对齐问题
    main_df.loc[update_mask, '经度'] = merged.loc[update_mask, '经度_new']
    main_df.loc[update_mask, '纬度'] = merged.loc[update_mask, '纬度_new']

main_df.to_excel(r'D:\xlzx\4G5G资料20220610\结果文件.xlsx', index=False)
unmatched = main_df[main_df['经度'].isna()]['铁塔站址编码'].unique()
print("未匹配的站址编码示例：", unmatched[:5])
all_tower_codes = set()
for df in sheets.values():
    codes = df['站址编码'].astype(str).str.strip().unique()
    all_tower_codes.update(codes)
print("铁塔文件包含唯一编码数：", len(all_tower_codes))